﻿namespace etracks.Web.Controllers
{
    using etracks.EntityModels;
    using etracks.Helpers;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using System.Web;
    using System.Web.Mvc;
    using System.Web.Mvc.Ajax;
    using System.Data.Linq;
    using System.Data.Common;

	[Authorize]
	public class ProjectController : BaseController
	{
		public ActionResult Index ()
		{
            ProjectIndexViewModel vm = new ProjectIndexViewModel();

            vm.Projects = ProjectController.GetProjectCount();

			return View (vm);
		}

        public ActionResult List()
        {
            ProjectListViewModel vm = new ProjectListViewModel();

            vm.ProjectList = ProjectController.GetProjectList();

            return this.List(vm);
        }

        public ActionResult View(int id)
        {
            ProjectReadOnlyViewModel vm = GetReadOnlyProject(id);

            vm.EnvironmentList = GetEnvironmentList(id);

            return View(vm);
        }

        public ActionResult Edit(int id)
        {
            ProjectInputFormViewModel vm = new ProjectInputFormViewModel();

            Project p = GetProject(id);

            if (p != null)
            {
                vm = new ProjectInputFormViewModel
                {
                    Id= p.Id,
                    Name = p.Name, 
                    Alias = p.Alias,
                    ProjectTypeId = p.ProjectTypeId
                };
            }

            vm.ProjectTypeList = GetProjectTypeList();

            return View(vm);
        }

        [HttpPost]
        public ActionResult Edit(ProjectInputFormViewModel vm)
        {
            try
            {
                Dictionary<string, object> parameters = new Dictionary<string,object>();
                parameters.Add("id", vm.Id);
                parameters.Add("name", vm.Name);
                parameters.Add("alias", vm.Alias);
                parameters.Add("project_type_id", vm.ProjectTypeId);

                int rows_affected = PostgresHelper.Execute("UPDATE project SET name = :name, alias = :alias, project_type_id = :project_type_id WHERE id = :id;", parameters);

                if (rows_affected > 0)
                    this.SuccessAlert("Project updated.");
            }
            catch (Exception)
            {
                throw;
            }

            vm.ProjectTypeList = GetProjectTypeList();
            return View(vm);
        }

        [HttpPost]
        public ActionResult Delete(int id)
        {
            try
            {
                Dictionary<string, object> parameters = new Dictionary<string, object>();
                parameters.Add("id", id);

                int rows_affected = PostgresHelper.Execute("DELETE FROM project WHERE id = :id;", parameters);

                if (rows_affected > 0)
                    this.SuccessAlert("Project deleted.");

                return RedirectToAction("List", "Project");
            }
            catch (Exception)
            {
                throw;
            }


            //return View();
            //return new EmptyResult();
            //return new HttpStatusCodeResult(System.Net.HttpStatusCode.InternalServerError, "Delete failed.");
        }


        private ActionResult List(ProjectListViewModel vm)
        {
            return View(vm);
        }

        public ActionResult Search()
        {
            return RedirectToAction("List");
        }

        [HttpPost]
        public ActionResult Search(string search_term)
        {
            ProjectSearchViewModel vm = new ProjectSearchViewModel();

            vm.Search_Term = search_term;

            vm.ProjectList = ProjectController.ProjectSearchResultsList(search_term);

            return View(vm);
        }

        public ActionResult Add()
        {
            etracks.Web.ProjectInputFormViewModel vm = new etracks.Web.ProjectInputFormViewModel();

            vm.ProjectTypeList = ProjectController.GetProjectTypeList();

            return View(vm);
        }

        [HttpPost]
        public ActionResult Add(ProjectInputFormViewModel vm)
        {
            // Validate vm
            if (string.IsNullOrEmpty(vm.Name))
                ModelState.AddModelError("Name", "Cannot be empty.");

            ModelState["Id"].Errors.Clear();

            if (ModelState.IsValid)
            {
                string new_id = RegisterProject(vm.Name, vm.Alias, vm.ProjectTypeId);

                this.SuccessAlert("Project added");
                
                return RedirectToAction("Edit", "Project", new { id = new_id });
            }

            vm.ProjectTypeList = ProjectController.GetProjectTypeList();

            return View(vm);
        }

        ////////////////////////////////////////////////////////////////////////////////
        // Data retrieval hack functions
        private static int GetProjectCount()
        {
            int result = 0;
            object db_result = PostgresHelper.GetObject("SELECT COUNT(*) FROM project;");

            if (db_result != null)
                int.TryParse(db_result.ToString(), out result);
            
            return result;
        }

        private static Project GetProject(int id)
        {
            //List<Project> result = new List<Project>();
            Project result = new Project();

            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("id", id);

            DataTable dt = PostgresHelper.GetDataTable("SELECT * FROM project WHERE id = :id ORDER BY id LIMIT 1;", parameters);

            if (dt != null)
            {
                result = dt.AsEnumerable().Select(n => new Project
                {
                    Id = n.Field<int>("id"),
                    Name = n.Field<string>("name"),
                    Alias = n.Field<string>("alias"),
                    ProjectTypeId = n.Field<int?>("project_type_id")
                }).FirstOrDefault();
            }

            return result;
        }

        private static ProjectReadOnlyViewModel GetReadOnlyProject(int id)
        {
            ProjectReadOnlyViewModel result = new ProjectReadOnlyViewModel();

            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("id", id);

            DataTable dt = PostgresHelper.GetDataTable(@"SELECT  p.*, pt.name AS ""project_type_name"" 
FROM    project p
LEFT OUTER JOIN 
        project_type pt
        ON p.project_type_id = pt.id
WHERE   p.id = :id;", parameters);

            if (dt != null)
            {
                result = dt.AsEnumerable().Select(n => new ProjectReadOnlyViewModel
                {
                    Id = n.Field<int>("id"),
                    Name = n.Field<string>("name"),
                    Alias = n.Field<string>("alias"),
                    ProjectTypeId = n.Field<int?>("project_type_id"),
                    ProjectType = n.Field<string>("project_type_name")
                }).FirstOrDefault();
            }

            return result;
        }

        private static List<Project> GetProjectList()
        {
            List<Project> result = new List<Project>();

            DataTable dt = PostgresHelper.GetDataTable("SELECT * FROM project ORDER BY id;");

            if (dt != null)
            {
                result = dt.AsEnumerable().Select(n => new Project
                {
                    Id = n.Field<int>("id"),
                    Name = n.Field<string>("name"),
                    Alias = n.Field<string>("alias")
                }).ToList();
            }

            return result;
        }

        private static List<Project> ProjectSearchResultsList(string search_term)
        {
            List<Project> result = new List<Project>();

            Dictionary<string, object> parameters = new Dictionary<string, object>();
            
            parameters.Add("search_term", string.Format("%{0}%", search_term));

            DataTable dt = PostgresHelper.GetDataTable(@"SELECT  p.*, pt.name AS ""project_type_name""
FROM    project p
LEFT OUTER JOIN
        project_type pt
        ON p.project_type_id = pt.id
WHERE   lower(p.name) LIKE lower(:search_term) 
        OR lower(p.alias) LIKE lower(:search_term)
        OR lower(pt.name) LIKE lower(:search_term)
ORDER BY id;", parameters);

            if (dt != null)
            {
                result = dt.AsEnumerable().Select(n => new Project
                {
                    Id = n.Field<int>("id"),
                    Name = n.Field<string>("name"),
                    Alias = n.Field<string>("alias")
                }).ToList();
            }

            return result;
        }

        private static string RegisterProject(string name, string alias, int? project_type_id)
        {
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("name", name);

            if (string.IsNullOrEmpty(alias))
                parameters.Add("alias", string.Empty);
            else
                parameters.Add("alias", alias);

            //parameters.Add("project_type_id", DataHelper<int?> project_type_id.Value);
            if (project_type_id.HasValue)
                parameters.Add("project_type_id", project_type_id.Value);
            else
                parameters.Add("project_type_id", DBNull.Value);

            string sql = "INSERT INTO Project (name, alias, project_type_id) VALUES (:name, :alias, :project_type_id) RETURNING id;";

            object db_result = PostgresHelper.GetObject(sql, parameters);

            if (db_result != null)
                return db_result.ToString();
            else
                return null;

        }

        private static List<ProjectType> GetProjectTypeList()
        {
            List<ProjectType> result = new List<ProjectType>();

            DataTable dt = PostgresHelper.GetDataTable("SELECT * FROM project_type ORDER BY id;");

            if (dt != null)
            {
                result = dt.AsEnumerable().Select(n => new ProjectType
                {
                    Id = n.Field<int>("id"),
                    Name = n.Field<string>("name")
                }).ToList();
            }

            return result;
        }

        private static List<etracks.EntityModels.Environment> GetEnvironmentList(int id)
        {
            List<etracks.EntityModels.Environment> result = new List<EntityModels.Environment>();

            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("project_id", id);

            DataTable dt = PostgresHelper.GetDataTable("SELECT * FROM environment WHERE project_id = :project_id;", parameters);

            if (dt != null)
            {
                result = dt.AsEnumerable().Select(n => new etracks.EntityModels.Environment
                {
                    Id = n.Field<int>("id"),
                    Name = n.Field<string>("name"),
                    ProjectId = n.Field<int?>("project_id")
                }).ToList();
            }

            return result;
        }
	}
}